<?
	ob_start();
	session_start();
	include("../lib/function.php");
	include("../lib/header.php");
	include("../lib/config.php");
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Pencarian Detail Pelamar</title>
</head>
<?
	$show	= $HTTP_GET_VARS['show'];
	$page 	= $HTTP_GET_VARS['page']; // get nomor halaman yang dipanggil
	$next	= $HTTP_GET_VARS['next']; // get segmen halaman yang dipanggil
	$pendidikan = $HTTP_GET_VARS['pendidikan'];
	$pengalaman	= $HTTP_GET_VARS['pengalaman'];
	$nama		= $HTTP_GET_VARS['nama'];
	$alamat		= $HTTP_GET_VARS['alamat'];
	$skill		= $HTTP_GET_VARS['skill'];
	$dari		= $HTTP_GET_VARS['dari'];
	$sampai		= $HTTP_GET_VARS['sampai'];
	$tgl_awal	= $HTTP_GET_VARS['tgl_awal'];
	$tgl_akhir	= $HTTP_GET_VARS['tgl_akhir'];
?>
<body>
<table width="100%" border="1">
 <tr class="headlist">
  <td>No</td>
  <td>NIK</td>
  <td>Nama</td>
  <td>Alamat</td>
  <td>Usia</td>
  <td>Pendidikan</td>
  <td>Pengalaman</td>
  <td>Kursus</td>
  <td>Tgl Lamaran</td>
 </tr>
 	<?
	$sqlccc  = "WITH ASD1 AS (select ROW_NUMBER() OVER(ORDER BY A.NIK ASC) AS No, A.NIK,Nama,Alamat,(year(getdate()) - year(Tgl_Lahir)) as Usia, datecreated, convert(char(12), DateCreated, 103) tgl_lamaran from karyawan A where pelamar='1' ";
	
	if(!empty($HTTP_GET_VARS['pendidikan'])){
		$sqlccc .= " AND A.NIK in (SELECT NIK FROM Pendidikan WHERE Pend_ID='".$HTTP_GET_VARS['pendidikan']."' AND NIK=A.NIK)";
	}
	
	if(!empty($HTTP_GET_VARS['pengalaman'])){
		$sqlccc .= " AND A.NIK in (SELECT NIK FROM Karir WHERE (jabatan LIKE '%".$HTTP_GET_VARS['pengalaman']."%' OR instansi LIKE '%".$HTTP_GET_VARS['pengalaman']."%') AND NIK=A.NIK) ";
	}
	
	if(!empty($HTTP_GET_VARS['skill'])){
		$sqlccc .= " AND A.NIK in (SELECT NIK FROM Kursus WHERE Program LIKE '%marketing%' AND NIK=A.NIK)";
	}
	
	$sqlccc .= " AND Nama LIKE '%".$HTTP_GET_VARS['nama']."%' AND Alamat LIKE '%".$HTTP_GET_VARS['alamat']."%'";

	if(!empty($HTTP_GET_VARS['dari']) || !empty($HTTP_GET_VARS['sampai'])){
		$sqlccc .= " AND ((year(getdate()) - year(Tgl_Lahir)) BETWEEN '".$HTTP_GET_VARS['dari']."' AND '".$HTTP_GET_VARS['sampai']."' ) ";
	}

	// pencarian berdasarkan periode tanggal
	// 01/12/2007
	// 01234567890
	// 
	$t1 = $_GET['tgl_awal'];
	$t2 = $_GET['tgl_akhir'];
	if(!empty($t1) && !empty($t2)){
		// konversi tanggal awal dmy to mdy
		$d = substr($t1,0,2);
		$m = substr($t1,3,2);
		$y = substr($t1,6,4);
		
		$taw = $m."/".$d."/".$y; 
		
		$d1 = substr($t2,0,2);
		$m1 = substr($t2,3,2);
		$y1 = substr($t2,6,4);
		
		$tak = $m1."/".$d1."/".$y1; 
		
		$sqlccc .= " AND (datecreated between '".$taw."' and '".$tak."') ";
	}

	$sqlccc.= " ) SELECT  * FROM ASD1";
	
		//$sqlccc .= " AND No BETWEEN $offset AND ".($limit+$offset)."";
		
		//echo $sqlccc;
		
		$sqlcccd = mssql_query($sqlccc,$conn);
		//echo mssql_num_rows($sqlcccd);
    	while($row = mssql_fetch_array($sqlcccd)){
			echo"
			 <tr class=contentlist>
			  <td align=right>$row[No]</td>
			  <td>$row[NIK]</td>
			  <td>$row[Nama]</td>
			  <td>$row[Alamat]</td>
			  <td>$row[Usia]</td>";
		// get pendidikan
			$sqlpend = mssql_query("	
			select NIK, A.Pend_ID, CASE keterangan 
				WHEN 'SD' THEN '1' 
				WHEN 'SD Lain-lain' THEN '1' 
				WHEN 'SMP' THEN '2' 
				WHEN 'SMP Lain-lain' THEN '2' 
				WHEN 'SMA' THEN '3' 
				WHEN 'SMK' THEN '3' 
				WHEN 'SMEA' THEN '3' 
				WHEN 'STM' THEN '3' 
				WHEN 'SMKK' THEN '3' 
				WHEN 'ST' THEN '3' 
				WHEN 'D1' THEN '4' 
				WHEN 'D2' THEN '5' 
				WHEN 'D3' THEN '6' 
				WHEN 'D4' THEN '7' 
				WHEN 'S1' THEN '8' 
				WHEN 'S2' THEN '9' 
				WHEN 'S3' THEN '10' 
			END jenjang, keterangan, jurusan  from pendidikan A, mstpendidikan B where A.Pend_ID=B.Pend_ID AND NIK='$row[NIK]' ORDER BY jenjang DESC",$conn);
			$rowpend = mssql_fetch_array($sqlpend);
			  echo"<td>$rowpend[keterangan], $rowpend[jurusan]</td>";
			  echo"<td>";
			  // get pengalaman
			  $skarir = mssql_query("SELECT * FROM Karir WHERE NIK='$row[NIK]'");
			  while($rkarir = mssql_fetch_array($skarir)){
			  	echo"&bull; $rkarir[jabatan], $rkarir[instansi] <br>";
			  }
			echo"</td><td>";
			  // get kursus
			  $skursus = mssql_query("SELECT * FROM Kursus WHERE NIK='$row[NIK]'");
			  while($rkursus = mssql_fetch_array($skursus)){
			  	echo"&bull; $rkursus[Program] <br>";
			  }
			echo"</td>  
			<td>$row[tgl_lamaran]</td>
			 </tr>
			";
		}
	?>
</table>
<?
	header('Content-type: application/vnd.ms-excel');
	header("Content-Disposition: attachment; filename=DATAPELAMAR.xls");
	header("Pragma: no-cache");
	header("Expires: 0");
?>